package com.tools.gensql.service;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.DbUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.Page;
import cn.hutool.db.PageResult;
import cn.hutool.db.ds.simple.SimpleDataSource;
import com.felipestanzani.jtoon.JToon;
import com.tools.gensql.model.ColumnSearchResultDTO;
import com.tools.gensql.model.ColumnVO;
import com.tools.gensql.model.ConnectionVO;
import com.tools.gensql.model.ExecuteQueryDTO;
import com.tools.gensql.model.GenerateSqlDTO;
import com.tools.gensql.model.LlmVO;
import com.tools.gensql.model.QueryResultDTO;
import com.tools.gensql.model.TableVO;
import lombok.extern.slf4j.Slf4j;
import org.noear.solon.Solon;
import org.noear.solon.ai.chat.ChatConfig;
import org.noear.solon.ai.chat.ChatModel;
import org.noear.solon.annotation.Component;
import org.noear.solon.annotation.Init;
import org.noear.solon.annotation.Inject;
import org.noear.solon.core.Props;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.Duration;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Component
@Slf4j
public class AppService {

    @Inject
    private ChatModel chatModel;

    @Inject
    private List<DataQuery> dataQueries;

    @Init
    public void initChatModel() {
        String apiUrl = getConfigValue("apiUrl");
        String provider = getConfigValue("provider");
        String model = getConfigValue("model");
        String apiKey = getConfigValue("apiKey");
        if (StrUtil.isNotBlank(apiUrl) && StrUtil.isNotBlank(model)) {
            ChatConfig config = new ChatConfig();
            config.setApiUrl(apiUrl);
            config.setProvider(provider);
            config.setModel(model);
            config.setApiKey(apiKey);
            config.setTimeout(Duration.ofMinutes(3));
            chatModel = ChatModel.of(config).build();
        }
    }

    private String getConfigValue(String key) {
        try {
            return DbUtil.use().queryString("select t.CONFIG_VALUE from APP_CONFIG t where t.CONFIG_KEY = ? limit 1", key);
        } catch (Exception e) {
            log.error("getConfigValue error", e);
            return null;
        }
    }

    public List<ConnectionVO> getConnections() {
        List<ConnectionVO> connectionVOS = new ArrayList<>();
        try {
            connectionVOS = DbUtil.use().query("select * from db_connection", ConnectionVO.class);
        } catch (SQLException e) {
            log.error("getConnections error", e);
            throw new RuntimeException(e);
        }
        connectionVOS.forEach(connectionVO -> {
            connectionVO.setPassword("******");
        });
        return connectionVOS;
    }

    public List<TableVO> getTables(Long connectionId) {
        if (connectionId == null) {
            return new ArrayList<>();
        }

        try {
            ConnectionVO connectionVO = getConnection(connectionId);
            DataQuery dataQuery = chooseDataQuery(connectionVO.getType());
            DataSource dataSource = buildDataSource(connectionVO, dataQuery);
            List<Entity> entities = DbUtil.use(dataSource).query(dataQuery.getTablesQuerySql(connectionVO.getCatalog()), connectionVO.getDatabase());
            if (CollUtil.isEmpty(entities)) {
                return new ArrayList<>();
            }

            return entities.stream().map(entity -> {
                TableVO tableVO = new TableVO();
                tableVO.setConnectionId(connectionId);
                tableVO.setName(entity.getStr("tableName"));
                tableVO.setComment(Convert.toStr(entity.getStr("tableComment"), tableVO.getName()));
                return tableVO;
            }).collect(Collectors.toList());
        } catch (Exception e) {
            log.error("getTables error", e);
            throw new RuntimeException(e);
        }
    }

    public ConnectionVO getConnection(Long connectionId) {
        try {
            Entity entityConn = DbUtil.use().queryOne("select * from db_connection where id = ?", connectionId);
            ConnectionVO connectionVO = entityConn.toBean(ConnectionVO.class);
            if (connectionVO == null) {
                throw new RuntimeException("数据库连接不存在");
            }
            return connectionVO;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private DataSource buildDataSource(ConnectionVO connectionVO, DataQuery dataQuery) {
        String url = dataQuery.getUrl(connectionVO.getHost(), connectionVO.getPort() + "", connectionVO.getDatabase(), connectionVO.getCatalog());
        return new SimpleDataSource(url, connectionVO.getUsername(), connectionVO.getPassword(), dataQuery.getDriver());
    }

    private DataQuery chooseDataQuery(String type) {
        for (DataQuery dataQuery : dataQueries) {
            if (dataQuery.getDatabaseType().getType().equals(type)) {
                return dataQuery;
            }
        }
        throw new RuntimeException("不支持的数据库类型");
    }

    public void saveConnection(ConnectionVO connectionVO) {
        log.info("保存数据库连接: {}", connectionVO.getName());
        log.info("类型: {}", connectionVO.getType());
        log.info("主机: {}", connectionVO.getHost());
        log.info("端口: {}", connectionVO.getPort());
        log.info("数据库: {}", connectionVO.getDatabase());
        log.info("用户名: {}", connectionVO.getUsername());
        log.info("密码: ********");
        log.info("catalog: {}", connectionVO.getCatalog());
        log.info("id: {}", connectionVO.getId());

        try {
            Entity record = Entity.create("db_connection")
                    .set("name", connectionVO.getName())
                    .set("type", connectionVO.getType())
                    .set("host", connectionVO.getHost())
                    .set("port", connectionVO.getPort())
                    .set("database", connectionVO.getDatabase())
                    .set("catalog", connectionVO.getCatalog())
                    .set("username", connectionVO.getUsername())
                    .set("password", connectionVO.getPassword());
            if (connectionVO.getId() != null && connectionVO.getId() > 0) {
                DbUtil.use().update(record, Entity.create("db_connection").set("id", connectionVO.getId()));
            } else {
                DbUtil.use().insert(record);
            }
        } catch (Exception e) {
            log.error("saveConnection error", e);
            throw new RuntimeException(e);
        }
    }

    public String generateSql(GenerateSqlDTO generateSqlDTO) {
        List<ColumnVO> columnVOList = new ArrayList<>();
        String dbType = "任意数据库";
        if (generateSqlDTO.getConnectionId() != null && CollUtil.isNotEmpty(generateSqlDTO.getTableNames())) {
            ConnectionVO connectionVO = getConnection(generateSqlDTO.getConnectionId());
            DataQuery dataQuery = chooseDataQuery(connectionVO.getType());
            DataSource dataSource = buildDataSource(connectionVO, dataQuery);
            dbType = connectionVO.getType();

            String sql = dataQuery.getQuerySql(generateSqlDTO.getTableNames().size(), connectionVO.getCatalog());
            List<String> params = new ArrayList<>();
            params.add(connectionVO.getDatabase());
            params.addAll(generateSqlDTO.getTableNames());
            try {
                columnVOList = DbUtil.use(dataSource).query(sql, ColumnVO.class, params.toArray());
            } catch (Exception e) {
                log.error("getTableStructure error", e);
                throw new RuntimeException(e);
            }
            if (CollUtil.isEmpty(columnVOList)) {
                columnVOList = new ArrayList<>();
            }
        }

        String template = """
                ## 角色与目标
                你是一名资深的数据工程师，擅长根据业务需求编写高质量、符合规范的SQL。
                你的任务是根据提供的数据库信息，将用户的自然语言问题精准地转换为可执行的SQL查询。
                
                ## 输入信息
                - 数据库类型：[{}]
                - 表结构：[{}]
                - 业务问题：[{}]
                
                ## 指令与约束
                请严格遵循以下要求生成SQL语句：
                1. 语法正确性：SQL必须完全符合指定数据库类型的语法规范。
                2. 逻辑准确性：查询逻辑必须精确对应业务问题，并基于提供的表结构。请仔细判断是否需要JOIN、使用何种聚合函数、以及正确的筛选条件。
                3. 代码质量：优先选择简洁、高效的写法。避免不必要的子查询或复杂函数，除非是实现需求所必需。
                4. 可读性：使用清晰的代码格式。在关键部分（如复杂的WHERE条件、JOIN逻辑、业务计算字段旁）添加简短注释，说明其目的。
                5. 输出格式：只输出最终的、完整的SQL代码，不要包含任何额外的解释、介绍或总结性文字，也不要包含任何Markdown格式的代码块。
                """;
        String prompt = StrUtil.format(template, dbType, JToon.encode(columnVOList), generateSqlDTO.getQuery());
        try {
            long time = System.currentTimeMillis();
            String content = chatModel.prompt(prompt).call().getContent();
            log.info("生成SQL语句: {}", content);
            log.info("耗时: {}ms", System.currentTimeMillis() - time);
            return content;
        } catch (Exception e) {
            log.error("生成SQL语句失败", e);
            throw new RuntimeException(e);
        }
    }
    
    public void deleteConnection(Long id) {
        if (id == null) {
            throw new RuntimeException("连接ID不能为空");
        }
        
        try {
            DbUtil.use().del("db_connection", "id", id);
        } catch (Exception e) {
            log.error("deleteConnection error", e);
            throw new RuntimeException(e);
        }
    }

    public void connection(Long connectionId) {
        if (connectionId == null) {
            throw new RuntimeException("连接ID不能为空");
        }

        try {
            ConnectionVO connectionVO = getConnection(connectionId);
            DataQuery dataQuery = chooseDataQuery(connectionVO.getType());
            DataSource dataSource = buildDataSource(connectionVO, dataQuery);
            DbUtil.use(dataSource).query("select 1");
        } catch (Exception e) {
            log.error("connection error", e);
            throw new RuntimeException(e);
        }
    }

    public void testConnection(ConnectionVO connectionVO) {
        try {
            DataQuery dataQuery = chooseDataQuery(connectionVO.getType());
            DataSource dataSource = buildDataSource(connectionVO, dataQuery);
            DbUtil.use(dataSource).query("select 1");
        } catch (Exception e) {
            log.error("testConnection error", e);
            throw new RuntimeException(e);
        }
    }

    public List<ColumnSearchResultDTO> searchColumns(Long connectionId, String keyword) {
        if (connectionId == null) {
            throw new RuntimeException("连接ID不能为空");
        }
        
        if (StrUtil.isBlank(keyword)) {
            throw new RuntimeException("搜索关键字不能为空");
        }

        try {
            ConnectionVO connectionVO = getConnection(connectionId);
            DataQuery dataQuery = chooseDataQuery(connectionVO.getType());
            DataSource dataSource = buildDataSource(connectionVO, dataQuery);

            String searchKeyword = "%" + keyword + "%";
            List<ColumnSearchResultDTO> result = DbUtil.use(dataSource).query(dataQuery.getQuerySql(connectionVO.getCatalog()), ColumnSearchResultDTO.class,
                    connectionVO.getDatabase(), searchKeyword, searchKeyword, searchKeyword, searchKeyword);
            
            // 处理nullable字段转换
            result.forEach(item -> {
                if (StrUtil.contains(item.getTableComment(), "\"")) {
                    item.setTableComment(StrUtil.replace(item.getTableComment(), "\"", "\\\""));
                }
                if (StrUtil.contains(item.getColumnComment(), "\"")) {
                    item.setColumnComment(StrUtil.replace(item.getColumnComment(), "\"", "\\\""));
                }
                item.setNullable("YES".equals(item.getIsNullAble()));
            });
            
            return result;
        } catch (Exception e) {
            log.error("searchColumns error", e);
            throw new RuntimeException(e);
        }
    }

    public LlmVO getModelSetting() {
        LlmVO llmVO = new LlmVO();
        String apiUrl = getConfigValue("apiUrl");
        String provider = getConfigValue("provider");
        String model = getConfigValue("model");
        String apiKey = getConfigValue("apiKey");
        llmVO.setApiUrl(apiUrl);
        llmVO.setProvider(provider);
        llmVO.setModel(model);
        llmVO.setApiKey(apiKey);
        if (StrUtil.isBlank(llmVO.getApiUrl())) {
            Props prop = Solon.cfg().getProp("solon.ai.chat.demo");
            if (prop != null) {
                llmVO.setApiUrl(prop.get("apiUrl"));
                llmVO.setProvider(prop.get("provider"));
                llmVO.setModel(prop.get("model"));
                llmVO.setApiKey(prop.get("apiKey"));
            }
        }
        return llmVO;
    }

    public void saveModelSetting(LlmVO llmVO) {
        if (llmVO == null) {
            throw new RuntimeException("模型设置不能为空");
        }
        setConfigValue("apiUrl", llmVO.getApiUrl());
        setConfigValue("provider", llmVO.getProvider());
        setConfigValue("model", llmVO.getModel());
        setConfigValue("apiKey", llmVO.getApiKey());
        // 重新初始化模型
        initChatModel();
    }

    private void setConfigValue(String key, String value) {
        if (StrUtil.isBlank(key)) {
            return;
        }
        try {
            DbUtil.use().insertOrUpdate(Entity.create("APP_CONFIG")
                    .set("config_key", key)
                    .set("config_value", value), "config_key");
        } catch (SQLException e) {
            log.error("setConfigValue error", e);
            throw new RuntimeException(e);
        }
    }

    public QueryResultDTO executeQuery(ExecuteQueryDTO executeQueryDTO) {
        if (executeQueryDTO.getConnectionId() == null) {
            throw new RuntimeException("连接ID不能为空");
        }
        
        if (StrUtil.isBlank(executeQueryDTO.getSql())) {
            throw new RuntimeException("SQL语句不能为空");
        }

        try {
            ConnectionVO connectionVO = getConnection(executeQueryDTO.getConnectionId());
            DataQuery dataQuery = chooseDataQuery(connectionVO.getType());
            DataSource dataSource = buildDataSource(connectionVO, dataQuery);

            String sql = StrUtil.format("select * from ({}) as gensql_temp",  executeQueryDTO.getSql());
            PageResult<Entity> page = DbUtil.use(dataSource).page(sql,
                    Page.of(executeQueryDTO.getCurrentPage() - 1, executeQueryDTO.getPageSize()));
            if (page == null) {
                page = new PageResult<>();
            }

            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            for (Entity entity : page) {
                entity.keySet().forEach(key -> {
                    Object value = entity.get(key);
                    if (value instanceof Timestamp) {
                        entity.set(key, ((Timestamp) value).toLocalDateTime().format(formatter));
                    } else if (value instanceof Date) {
                        entity.set(key, DateUtil.formatDate((Date) value));
                    } else {
                        entity.set(key, value);
                    }
                });
            }

            // 转换为Map列表
            List<Map<String, Object>> records = new ArrayList<>(page);

            QueryResultDTO result = new QueryResultDTO();
            result.setRecords(records);
            result.setTotalRecords(page.getTotal());
            result.setCurrentPage(page.getPage());
            result.setPageSize(page.getPageSize());
            result.setTotalPages(page.getTotalPage());

            return result;
        } catch (Exception e) {
            log.error("执行SQL查询失败", e);
            throw new RuntimeException("执行SQL查询失败: " + e.getMessage());
        }
    }
}